package com.module.ble.db

import androidx.room.Dao
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import androidx.room.RawQuery
import androidx.room.Update
import androidx.sqlite.db.SupportSQLiteQuery
import com.module.ble.db.table.UserSleepInfo
import kotlinx.coroutines.flow.Flow

/**
 * 作者：sosou
 * <p>
 * 版本：1.0
 * <p>
 * 创建日期：2025/3/25
 * <p>
 * 描述：
 * <p>
 * 修订历史：
 */
@Dao
interface SleepInfoDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(vararg bean: UserSleepInfo)

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(mList:List<UserSleepInfo>)

    @Query("SELECT createdTime FROM sleep_info WHERE createdTime IN (:times)")
    fun getExistingTimes(times: List<Long>): List<Long>

    @Query("SELECT * FROM sleep_info")
    fun findAll(): MutableList<UserSleepInfo>

    @Query("SELECT * FROM sleep_info WHERE sysStat = 0")
    fun findAllNotUp(): MutableList<UserSleepInfo>

    @Query("SELECT * FROM sleep_info")
    fun findAllFlow(): Flow<List<UserSleepInfo>>

    // 先获取最新日期，再查询该日期的所有数据
    @Query("SELECT * FROM sleep_info WHERE date(createdTime/1000, 'unixepoch', 'localtime') = (SELECT date(MAX(createdTime)/1000, 'unixepoch', 'localtime') FROM sleep_info) ORDER BY createdTime DESC")
    fun findLatestDayRecords(): List<UserSleepInfo>

    @Query("SELECT * FROM sleep_info WHERE date(createdTime/1000, 'unixepoch', 'localtime') = (SELECT date(MAX(createdTime)/1000, 'unixepoch', 'localtime') FROM sleep_info) ORDER BY createdTime DESC")
    fun findLatestDayFlow(): Flow<List<UserSleepInfo>>

    // 根据时间范围查询数据
    @Query("SELECT * FROM sleep_info WHERE createdTime >= :startTime AND createdTime <= :endTime ORDER BY createdTime ASC")
    fun findByTimeRange(startTime: Long, endTime: Long): List<UserSleepInfo>

    // 根据时间范围查询夜间睡眠数据
    @Query("SELECT * FROM sleep_info WHERE createdTime >= :startTime AND createdTime <= :endTime AND sleepType = 0 ORDER BY createdTime ASC")
    fun findByTimeRangeByNight(startTime: Long, endTime: Long): List<UserSleepInfo>

    // 根据时间范围查询小睡数据
    @Query("SELECT * FROM sleep_info WHERE createdTime >= :startTime AND createdTime <= :endTime AND sleepType = 1 ORDER BY createdTime ASC")
    fun findByTimeRangeByNap(startTime: Long, endTime: Long): List<UserSleepInfo>

    // 根据时间范围查询数据
    @Query("SELECT * FROM sleep_info WHERE createdTime >= :startTime AND createdTime <= :endTime ORDER BY createdTime ASC")
    fun findByTimeRangeFlow(startTime: Long, endTime: Long): Flow<List<UserSleepInfo>>

    // 查询 所有  倒序 查询
    @Query("SELECT * FROM sleep_info ORDER BY createdTime DESC  LIMIT :mSize")
    fun findByDescSize(mSize: Int): MutableList<UserSleepInfo>

    //查询某条数据
    @Query("SELECT * FROM sleep_info WHERE createdTime = :time")
    fun findMessageByTime(time:Long): UserSleepInfo

    @RawQuery
    fun findByCustomQuery(query: SupportSQLiteQuery): List<UserSleepInfo>

    @RawQuery(observedEntities = [UserSleepInfo::class])
    fun findByCustomQueryFlow(query: SupportSQLiteQuery): Flow<List<UserSleepInfo>>

    /**
     * 根据指定时间戳查询该时间戳当天及前6天的所有数据
     * @param timestamp 指定的时间戳（毫秒）
     * @return 7天内的睡眠数据列表
     */
    @Query("SELECT * FROM sleep_info WHERE date(createdTime/1000, 'unixepoch', 'localtime') >= date(:timestamp/1000 - 6*24*3600, 'unixepoch', 'localtime') AND date(createdTime/1000, 'unixepoch', 'localtime') <= date(:timestamp/1000, 'unixepoch', 'localtime') ORDER BY createdTime ASC")
    fun findByTimestampLast7Days(timestamp: Long): List<UserSleepInfo>

    /**
     * 根据指定时间戳查询该时间戳当天及前6天的所有数据（Flow版本）
     * @param timestamp 指定的时间戳（毫秒）
     * @return Flow<List<UserSleepInfo>>
     */
    @Query("SELECT * FROM sleep_info WHERE date(createdTime/1000, 'unixepoch', 'localtime') >= date(:timestamp/1000 - 6*24*3600, 'unixepoch', 'localtime') AND date(createdTime/1000, 'unixepoch', 'localtime') <= date(:timestamp/1000, 'unixepoch', 'localtime') ORDER BY createdTime ASC")
    fun findByTimestampLast7DaysFlow(timestamp: Long): Flow<List<UserSleepInfo>>

    /**
     * 根据指定时间戳查询该时间戳前7天的所有数据（不包含今天）
     * @param timestamp 指定的时间戳（毫秒）
     * @return 前7天内的睡眠数据列表
     */
    @Query("SELECT * FROM sleep_info WHERE date(createdTime/1000, 'unixepoch', 'localtime') >= date(:timestamp/1000 - 7*24*3600, 'unixepoch', 'localtime') AND date(createdTime/1000, 'unixepoch', 'localtime') < date(:timestamp/1000, 'unixepoch', 'localtime') ORDER BY createdTime ASC")
    fun findByTimestampLast7DaysNotTheDay(timestamp: Long): List<UserSleepInfo>

    /**
     * 根据指定时间戳查询该时间戳前7天的所有数据（不包含今天）（Flow版本）
     * @param timestamp 指定的时间戳（毫秒）
     * @return Flow<List<UserSleepInfo>>
     */
    @Query("SELECT * FROM sleep_info WHERE date(createdTime/1000, 'unixepoch', 'localtime') >= date(:timestamp/1000 - 7*24*3600, 'unixepoch', 'localtime') AND date(createdTime/1000, 'unixepoch', 'localtime') < date(:timestamp/1000, 'unixepoch', 'localtime') ORDER BY createdTime ASC")
    fun findByTimestampLast7DaysNotTheDayFlow(timestamp: Long): Flow<List<UserSleepInfo>>

    /**
     * 根据指定时间戳查询该时间戳当天及前6天的夜间睡眠数据
     * @param timestamp 指定的时间戳（毫秒）
     * @return 7天内的夜间睡眠数据列表
     */
    @Query("""
        SELECT * FROM sleep_info 
        WHERE date(createdTime/1000, 'unixepoch', 'localtime') >= date(:timestamp/1000 - 6*24*3600, 'unixepoch', 'localtime')
        AND date(createdTime/1000, 'unixepoch', 'localtime') <= date(:timestamp/1000, 'unixepoch', 'localtime')
        AND sleepType = 0
        ORDER BY createdTime ASC
    """)
    fun findByTimestampLast7DaysNight(timestamp: Long): List<UserSleepInfo>

    /**
     * 根据指定时间戳查询该时间戳当天及前6天的小睡数据
     * @param timestamp 指定的时间戳（毫秒）
     * @return 7天内的小睡数据列表
     */
    @Query("""
        SELECT * FROM sleep_info 
        WHERE date(createdTime/1000, 'unixepoch', 'localtime') >= date(:timestamp/1000 - 6*24*3600, 'unixepoch', 'localtime')
        AND date(createdTime/1000, 'unixepoch', 'localtime') <= date(:timestamp/1000, 'unixepoch', 'localtime')
        AND sleepType = 1
        ORDER BY createdTime ASC
    """)
    fun findByTimestampLast7DaysNap(timestamp: Long): List<UserSleepInfo>

    /**
     * 根据指定时间戳查询该时间戳前7天的夜间睡眠数据（不包含今天）
     * @param timestamp 指定的时间戳（毫秒）
     * @return 前7天内的夜间睡眠数据列表
     */
    @Query("""
        SELECT * FROM sleep_info 
        WHERE date(createdTime/1000, 'unixepoch', 'localtime') >= date(:timestamp/1000 - 7*24*3600, 'unixepoch', 'localtime')
        AND date(createdTime/1000, 'unixepoch', 'localtime') < date(:timestamp/1000, 'unixepoch', 'localtime')
        AND sleepType = 0
        ORDER BY createdTime ASC
    """)
    fun findByTimestampLast7DaysNotTheDayNight(timestamp: Long): List<UserSleepInfo>

    /**
     * 根据指定时间戳查询该时间戳前7天的小睡数据（不包含今天）
     * @param timestamp 指定的时间戳（毫秒）
     * @return 前7天内的小睡数据列表
     */
    @Query("""
        SELECT * FROM sleep_info 
        WHERE date(createdTime/1000, 'unixepoch', 'localtime') >= date(:timestamp/1000 - 7*24*3600, 'unixepoch', 'localtime')
        AND date(createdTime/1000, 'unixepoch', 'localtime') < date(:timestamp/1000, 'unixepoch', 'localtime')
        AND sleepType = 1
        ORDER BY createdTime ASC
    """)
    fun findByTimestampLast7DaysNotTheDayNap(timestamp: Long): List<UserSleepInfo>

    /**
     * 根据指定时间戳查询该时间戳当天内sleepType=1的所有数据
     * @param timestamp 指定的时间戳（毫秒）
     * @return 当天内sleepType=1的所有数据列表
     */
    @Query("""SELECT * FROM sleep_info 
             WHERE date(createdTime/1000, 'unixepoch', 'localtime') = date(:timestamp/1000, 'unixepoch', 'localtime') 
             AND sleepType = 1 
             ORDER BY createdTime ASC""")
    fun findByTimestampSameDayWithNapSleep(timestamp: Long): List<UserSleepInfo>

    /**
     * 根据指定时间戳查询该时间戳当天内sleepType=1的所有数据（Flow版本）
     * @param timestamp 指定的时间戳（毫秒）
     * @return Flow<List<UserSleepInfo>>
     */
    @Query("""SELECT * FROM sleep_info 
             WHERE date(createdTime/1000, 'unixepoch', 'localtime') = date(:timestamp/1000, 'unixepoch', 'localtime') 
             AND sleepType = 1 
             ORDER BY createdTime ASC""")
    fun findByTimestampSameDayWithNapSleepFlow(timestamp: Long): Flow<List<UserSleepInfo>>

    @Update
    fun update(msg: UserSleepInfo)

    @Update
    fun update(mList:List<UserSleepInfo>)

    @Query("select count(*) from sleep_info")
    fun count(): Int

    @Delete
    fun delete(data: UserSleepInfo)

    //删除所有 → '所有'的只能用@Query,@Query可以编写任意的sql语句
    @Query("DELETE FROM sleep_info")
    fun deleteAll()

    @Query("DELETE FROM sleep_info WHERE createdTime = :time")
    fun deleteMsgByTime(time:Long)

    /**
     * 将所有数据的sysStat更新为0
     */
    @Query("UPDATE sleep_info SET sysStat = 0")
    fun updateAllSysStatToZero()

    /**
     * 将所有数据的sleepType更新为0
     */
    @Query("UPDATE sleep_info SET sleepType = 0")
    fun updateAllSleepTypeToZero()

    /**
     * 将 firmName = "1" 的所有数据的 sleepType 更新为 0
     */
    @Query("UPDATE sleep_info SET sleepType = 0 WHERE firmName = '1'")
    fun updateSleepTypeToZeroForFirm1()
}